/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Helper.AccountHelper;
import Helper.DepartmentHelper;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author KIENLT
 */
public class DepartmentDAO {
    public static ResultSet fillAllDepartment(Connection cnn) throws Exception {
        String strSQL = "{call fillAllDepartment}";
        CallableStatement stmt = cnn.prepareCall(strSQL);
        return stmt.executeQuery();
    }
    
    public static ArrayList<DepartmentHelper> fillAll(Connection cnn) throws Exception{
        ArrayList<DepartmentHelper> arrDepartmentList = new ArrayList<DepartmentHelper>();
        DepartmentHelper departmentData = null;
        String strSQL = "{call fillAllDepartment}";
        CallableStatement stmt = cnn.prepareCall(strSQL);
        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            departmentData = new DepartmentHelper();
            departmentData.setDepartmentID(rs.getString("DepartmentId"));
            departmentData.setDepartmentName(rs.getString("DepartmentName"));
            arrDepartmentList.add(departmentData);
        }
        return arrDepartmentList;
    }
    
    public static ResultSet FillAll(Connection cnn) throws Exception{
        String strSQL = "SELECT * FROM tblDepartment";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        return stmt.executeQuery();
    }
    
    public static ResultSet FillOne(Connection cnn, DepartmentHelper obj) throws Exception {
        String strSQL = "Select * from tblDepartment where DepartmentId=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getDepartmentID());
        return stmt.executeQuery();
    }
    public static int Insert(Connection cnn, DepartmentHelper obj) throws Exception{
        String strSQL = "insert into tblDepartment(DepartmentName,Status) values(?,?)";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
    //    stmt.setString(1, obj.getDepartmentID());
        stmt.setString(1, obj.getDepartmentName());
        stmt.setString(2, obj.getDepartmentStatus());
        return stmt.executeUpdate();
    }
     public static boolean checkDepartmentName(Connection cnn, DepartmentHelper obj) throws Exception {
        boolean check = false;
        String strSQL = "select * from tblDepartment where DepartmentName = ?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getDepartmentName());
        ResultSet rs = stmt.executeQuery();
        if(rs.next()){
            check = true;
        }
        return check;
    }
     public static int UpdateDep(Connection cnn, DepartmentHelper obj) throws Exception {
        String strSQL = " Update tblDepartment SET DepartmentName = ?, Status = ? WHERE DepartmentId = ?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
      
        stmt.setString(1, obj.getDepartmentName());
        stmt.setString(2, obj.getDepartmentStatus());
        stmt.setString(3, obj.getDepartmentID());
        return stmt.executeUpdate();
    }
      public static int Delete(Connection cnn, DepartmentHelper obj) throws Exception{
        String strSQL = "DELETE FROM tblDepartment WHERE DepartmentId = ?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getDepartmentID());
        return stmt.executeUpdate();
    }

}
